package com.jms.fu.model.dao;

import java.io.UnsupportedEncodingException;
import java.security.NoSuchAlgorithmException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.jms.fu.common.Common;
import com.jms.fu.exception.DAOException;
import com.jms.fu.model.bean.Transaction;

public class TransactionDAO {
	Connection conn = null;
	PreparedStatement pStm = null;
	Statement stm = null;
	ResultSet rs = null;

	public List<Transaction> getAllTransaction() throws DAOException {
		Transaction temp;
		ArrayList<Transaction> list = new ArrayList<>();
		try {
			conn = ConnectionFactory.getConnection();
			String sql = "SELECT * FROM tb_Transaction";
			pStm = conn.prepareStatement(sql);
			rs = pStm.executeQuery();
			while (rs.next()) {
				temp = new Transaction();
				temp.setTransactionID(rs.getInt(1));
				temp.setAppID(rs.getInt(2));
				temp.setUserID(rs.getString(3));
				temp.setTotal(rs.getFloat(4));
				temp.setDateCreate((Date) rs.getDate(5));
				temp.setType(rs.getInt(6));
				temp.setStatus(rs.getInt(7));
				list.add(temp);
			}
			return list;
		} catch (SQLException e) {
			throw new DAOException(e.getMessage());
		} finally {
			try {
				if (rs != null) {
					rs.close();
				}
				if (pStm != null) {
					pStm.close();
				}
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException e) {
				throw new DAOException(e.getMessage());
			}
		}
	}

	public void changeStatus(String transID) throws DAOException {
		try {

			conn = ConnectionFactory.getConnection();
			String sql = "UPDATE tb_Transaction SET status=0 WHERE transactionID=?";

			pStm = conn.prepareStatement(sql);
			pStm.setString(1, transID);
			pStm.executeUpdate();

			pStm.executeUpdate();
		} catch (SQLException e) {
			throw new DAOException(e.getMessage());
		} finally {
			try {
				if (rs != null) {
					rs.close();
				}
				if (pStm != null) {
					pStm.close();
				}
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException e) {
				throw new DAOException(e.getMessage());
			}
		}

	}

	public void changeStatus1(String transID) throws DAOException {
		try {

			conn = ConnectionFactory.getConnection();
			String sql = "UPDATE tb_Transaction SET status=1 WHERE transactionID=?";

			pStm = conn.prepareStatement(sql);
			pStm.setString(1, transID);
			pStm.executeUpdate();

			pStm.executeUpdate();
		} catch (SQLException e) {
			throw new DAOException(e.getMessage());
		} finally {
			try {
				if (rs != null) {
					rs.close();
				}
				if (pStm != null) {
					pStm.close();
				}
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException e) {
				throw new DAOException(e.getMessage());
			}
		}

	}

	public void addNewTransaction(String username, int appID, float amount,
			int status, int type) throws DAOException {
		try {

			conn = ConnectionFactory.getConnection();
			String sql = "INSERT INTO tb_Transaction (appID,userID,total,dateCreate,type,status) VALUES (?,?,?,?,?,?)";

			pStm = conn.prepareStatement(sql);
			pStm.setInt(1, appID);
			pStm.setString(2, username);
			pStm.setFloat(3, amount);
			Date date = new Date();
			pStm.setDate(4, new java.sql.Date(date.getTime()));
			pStm.setInt(5, type);
			pStm.setInt(6, status);
			pStm.executeUpdate();

		} catch (SQLException e) {
			throw new DAOException(e.getMessage());
		} finally {
			try {
				if (rs != null) {
					rs.close();
				}
				if (pStm != null) {
					pStm.close();
				}
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException e) {
				throw new DAOException(e.getMessage());
			}
		}

	}

	public String getBuyAppToChart() throws DAOException {
		ArrayList<String> date = new ArrayList<>();
		ArrayList<String> count = new ArrayList<>();
		String out = "";
		try {

			conn = ConnectionFactory.getConnection();
			String sql = "SELECT dateCreate,COUNT(transactionID) AS Tong FROM tb_Transaction  WHERE type=0 GROUP BY dateCreate";

			pStm = conn.prepareStatement(sql);
			rs=pStm.executeQuery();
			while (rs.next()) {
				date.add(rs.getDate(1).toString());
				count.add(rs.getString(2));
			}
			int k = date.size();
			
			for (int i = 0; i < k; i++) {
				if (i == (k - 1)) {
					out += "[\"" + date.get(i) + "\"," + count.get(i) + "]";
				} else{
					out += "[\"" + date.get(i) + "\"," + count.get(i) + "],";

				}
				
			}
			return out;
		} catch (SQLException e) {
			throw new DAOException(e.getMessage());
		} finally {
			try {
				if (rs != null) {
					rs.close();
				}
				if (pStm != null) {
					pStm.close();
				}
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException e) {
				throw new DAOException(e.getMessage());
			}
		}

	}

	public String getDepositToChart() throws DAOException {
		ArrayList<String> date = new ArrayList<>();
		ArrayList<String> count = new ArrayList<>();
		String out = "";
		try {

			conn = ConnectionFactory.getConnection();
			String sql = "SELECT dateCreate,COUNT(transactionID) AS Tong FROM tb_Transaction  WHERE type=1 GROUP BY dateCreate";

			pStm = conn.prepareStatement(sql);
			rs=pStm.executeQuery();
			while (rs.next()) {
				date.add(rs.getDate(1).toString());
				count.add(rs.getString(2));
			}
			int k = date.size();

			for (int i = 0; i < k; i++) {
				if (i == (k - 1)) {
					out += "[\"" + date.get(i) + "\"," + count.get(i) + "]";
				} else{
					out += "[\"" + date.get(i) + "\"," + count.get(i) + "],";

				}
				}
			return out;
			
			
		} catch (SQLException e) {
			throw new DAOException(e.getMessage());
		} finally {
			try {
				if (rs != null) {
					rs.close();
				}
				if (pStm != null) {
					pStm.close();
				}
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException e) {
				throw new DAOException(e.getMessage());
			}
		}
	}

	public String getRequestMoneyToChart() throws DAOException {
		ArrayList<String> date = new ArrayList<>();
		ArrayList<String> count = new ArrayList<>();
		String out = "";
		try {

			conn = ConnectionFactory.getConnection();
			String sql = "SELECT dateCreate,COUNT(transactionID) AS Tong FROM tb_Transaction  WHERE type=2 GROUP BY dateCreate";

			pStm = conn.prepareStatement(sql);
			rs=pStm.executeQuery();
			while (rs.next()) {
				date.add(rs.getDate(1).toString());
				count.add(rs.getString(2));
			}
			int k = date.size();
			for (int i = 0; i < k; i++) {
				if (i == (k - 1)) {
					out += "[\"" + date.get(i) + "\"," + count.get(i) + "]";
				} else{
					out += "[\"" + date.get(i) + "\"," + count.get(i) + "],";

				}

			}
			return out;
		} catch (SQLException e) {
			throw new DAOException(e.getMessage());
		} finally {
			try {
				if (rs != null) {
					rs.close();
				}
				if (pStm != null) {
					pStm.close();
				}
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException e) {
				throw new DAOException(e.getMessage());
			}
		}	}

	public String getTotalDownloadToChart() throws DAOException {
		ArrayList<String> date = new ArrayList<>();
		ArrayList<String> count = new ArrayList<>();
		String out = "";
		try {

			conn = ConnectionFactory.getConnection();
			String sql = "SELECT datedownload,COUNT(appDownId) FROM tb_AppDown GROUP BY datedownload";

			pStm = conn.prepareStatement(sql);
			rs=pStm.executeQuery();
			while (rs.next()) {
				date.add(rs.getDate(1).toString());
				count.add(rs.getString(2));
			}
			int k = date.size();
			for (int i = 0; i < k; i++) {
				if (i == (k - 1)) {
					out += "[\"" + date.get(i) + "\"," + count.get(i) + "]";
				} else{
					out += "[\"" + date.get(i) + "\"," + count.get(i) + "],";

				}

			}
			return out;
		} catch (SQLException e) {
			throw new DAOException(e.getMessage());
		} finally {
			try {
				if (rs != null) {
					rs.close();
				}
				if (pStm != null) {
					pStm.close();
				}
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException e) {
				throw new DAOException(e.getMessage());
			}
		}
	}

	public String getTotalUploadToChart() throws DAOException{
		ArrayList<String> date = new ArrayList<>();
		ArrayList<String> count = new ArrayList<>();
		String out = "";
		try {

			conn = ConnectionFactory.getConnection();
			String sql = "SELECT dateUpload,COUNT(appId) FROM tb_App GROUP BY dateUpload";

			pStm = conn.prepareStatement(sql);
			rs=pStm.executeQuery();
			while (rs.next()) {
				date.add(rs.getDate(1).toString());
				count.add(rs.getString(2));
			}
			int k = date.size();
			for (int i = 0; i < k; i++) {
				if (i == (k - 1)) {
					out += "[\"" + date.get(i) + "\"," + count.get(i) + "]";
				} else{
					out += "[\"" + date.get(i) + "\"," + count.get(i) + "],";

				}

			}
			return out;
		} catch (SQLException e) {
			throw new DAOException(e.getMessage());
		} finally {
			try {
				if (rs != null) {
					rs.close();
				}
				if (pStm != null) {
					pStm.close();
				}
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException e) {
				throw new DAOException(e.getMessage());
			}
		}
	}
}
